﻿namespace KeppelLand.Services
{
    using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Caching;
using System.Web.Hosting;

    /// <summary>
    /// Summary description for TestJobHost
    /// </summary>
    public class RecurringJobHost : IRegisteredObject
    {
        public static System.Web.Caching.CacheItemRemovedCallback OnCacheRemove = null;

        private readonly object _lock = new object();
        private bool _shuttingDown;

        public RecurringJobHost()
        {
            HostingEnvironment.RegisterObject(this);
        }

        public void Stop(bool immediate)
        {
            lock (_lock)
            {
                _shuttingDown = true;
            }
            HostingEnvironment.UnregisterObject(this);
        }

        public void DoWork(Action work)
        {
            lock (_lock)
            {
                if (_shuttingDown)
                {
                    return;
                }
                work();
            }
        }

        public static void SyncWithB2E()
        {
            System.Data.DataSet ds = new System.Data.DataSet("CurrentClaims");
            System.Data.DataTable dtB2E = null;

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=kldb;Initial Catalog=B2EPilot;User ID=b2epilotwebacct;Password=p@55w0rd;Application Name=Staff-Overtime;Workstation ID=AN2594"))
            {
                System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText =
@"SELECT  e.EmployeeMaster, b.Intranet_ID, p.NameLegal, a.Section, e.AccessLevel, NULLIF(RTRIM(LTRIM(p.Internet)), '') AS 'Email'
FROM    tblDataB2E_SSO b 
INNER JOIN 
        tblDataPersonnelB2E e 
        ON b.Intranet_ID = e.LoginID 
INNER JOIN 
        tblDataPersonnel p 
        ON e.EmployeeMaster = p.EmployeeMaster
INNER JOIN 
        tblDataActivation a 
        ON p.EmployeeMaster = a.EmployeeMaster";

                System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);
                da.Fill(ds, "B2E");

                if ((ds != null) && (ds.Tables.Count > 0) && (ds.Tables["B2E"].Rows.Count > 0))
                {
                    dtB2E = ds.Tables["B2E"];
                }
            }

            if (dtB2E != null)
            {
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
                {
                    System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();

                    conn.Open();
                    using (System.Data.SqlClient.SqlTransaction sqlTran = conn.BeginTransaction())
                    {
                        cmd.CommandText = "DELETE FROM B2E";
                        cmd.Transaction = sqlTran;
                        cmd.ExecuteNonQuery();

                        using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(conn, System.Data.SqlClient.SqlBulkCopyOptions.Default, sqlTran))
                        {
                            bcp.DestinationTableName = "B2E";
                            bcp.WriteToServer(dtB2E);
                        }

                        sqlTran.Commit();

                        KeppelLand.Helpers.EmailHelper.SendStartEndAlertEmail("B2E Sync");
                    }
                }
            }
        }

        public static DateTime GetFriday(int mod)
        {
            DateTime today = DateTime.Today.AddDays(mod);
            // The (... + 7) % 7 ensures we end up with a value in the range [0, 6]
            int daysUntilFriday = ((int)DayOfWeek.Friday - (int)today.DayOfWeek + 7) % 7;
            DateTime nextFriday = today.AddDays(daysUntilFriday);
            return nextFriday;
        }

        public static DateTime NextDayOfWeek(string dayOfWeekName)
        {
            DateTime nextTargetDayOfWeek = DateTime.MaxValue;
            DayOfWeek targetDayOfWeek;
            string targetDayOfWeekName = string.Empty;

            if (string.IsNullOrEmpty(dayOfWeekName))
                return nextTargetDayOfWeek;
            
            targetDayOfWeekName = System.Globalization.CultureInfo.InvariantCulture.TextInfo.ToTitleCase(dayOfWeekName.ToLowerInvariant());

            if (Enum.TryParse<DayOfWeek>(targetDayOfWeekName, out targetDayOfWeek))
            {
                DateTime today = DateTime.Today;
                int daysUntilNextTargetDayOfWeek = ((int)targetDayOfWeek - (int)today.DayOfWeek + 7) % 7;
                nextTargetDayOfWeek = today.AddDays(daysUntilNextTargetDayOfWeek);
            }

            return nextTargetDayOfWeek;
        }

        public static bool Is7DaysBeforeCutoffDate(DateTime targetDate)
        {
            bool result = false;

            string connectionString = @"Data Source=KLDB3\SQL2008;Initial Catalog=CDMS_UAT;Persist Security Info=True;User ID=CDMS_UAT_user;Password=Xh57887266;MultipleActiveResultSets=True;Application Name=Staff-Overtime;Workstation ID=AN2594";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"SELECT TOP 1 DATEADD([DAY], -7, EndDate) AS 'NotificationDate',* 
FROM    ReportingPeriod 
WHERE   Locked = 1
OR
(LockedDate IS NULL AND NOT EXISTS (SELECT * FROM ReportingPeriod WHERE Locked = 1))
ORDER BY EndDate";

                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                if ((ds.Tables.Count > 0) && (ds.Tables[0].Rows.Count > 0))
                {
                    DateTime dailyReminderStartDate = ds.Tables[0].Rows[0].Field<DateTime>("NotificationDate");
                    DateTime dailyReminderEndDate = ds.Tables[0].Rows[0].Field<DateTime>("EndDate");

                    if ((targetDate >= dailyReminderStartDate) && (targetDate < dailyReminderEndDate))
                    {
                        result = true;
                    }

                }

            }

            return result;
        }

        public static void SendReminder()
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EOCS"].ConnectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                
                cmd.CommandText = @"SELECT  B2E.*
FROM    B2E 
INNER JOIN 
(
    SELECT DISTINCT RecommenderId AS 'EmployeeMaster' FROM Claim WHERE (Status = 1)
    UNION
    SELECT DISTINCT ApproverId FROM Claim WHERE (Status = 3)
) emp
ON B2E.EmployeeMaster = emp.EmployeeMaster";

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                DataTable dt = ds.Tables[0];

                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                // Form content of the reminder email.
                sb.AppendLine();
                sb.AppendFormat("<p>Please note that you have outstanding claims that are pending your approval in the staff overtime application.</p>");
                sb.AppendFormat("<p>Click <a href='klwfe03/B2E/e-overtime/'>here</a> to access the staff overtime application.</p>");
                sb.AppendLine();

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        //KeppelLand.Helpers.EmailHelper.SendEmail(
                        //    "zhixian.ong@kepland.com.sg", "Staff Overtime Reminder", sb.ToString() + 
                        //    string.Format("<p>Targeted at {0}, email: {1}</p>", dr["NameLegal"], dr["Email"]), true);
                        KeppelLand.Helpers.EmailHelper.SendEmail(
                            dr["Email"].ToString(), "Staff Overtime Reminder", sb.ToString(), true);
                    }
                }

            }
        }
    }
}